An OLAP database may be described as a set of data that is understood in terms of attributes or dimensions. For example, a 3-dimensional OLAP database may store data for the following three dimensions: Product, Market, and Time. The OLAP database may be populated with data that is stored in a relational database. An intersection of a set of dimensions may be described as a cell having a cell value (i.e., a measure). Also, dimensions may be organized as a hierarchy. Dimensions may also have members that are used as keys to access cell values. For example, members may be specific products, customers, time periods, geographic regions, etc.
A type of OLAP database is a Relational OLAP (ROLAP) database. A Relational OLAP database may be described as an OLAP model built on top of a relational database. A Relational OLAP model includes an OLAP model definition and describes how the OLAP model objects map to relational model objects (e.g., tables and columns).
Multidimensional Expressions (MDX) may be described as a query language for inserting, updating, and retrieving data from an OLAP database. The OLAP database translates the MDX query into one or more SQL queries against the relational database to retrieve the member and cell values to return as part of executing an MDX query.
An MDX query includes one or more expressions. Before executing (i.e., evaluating) the query, it is difficult to determine what is required from the relational database because the execution of some expressions are dependent on the results of executing some other expressions. Trying to pre-load data from the relational database into the OLAP database before execution of any MDX expression may lead to loading the entire relational database in memory or to executing the query itself or part of it.
On the other hand, accessing the relational database on-demand while executing the MDX query ensures loading members and cell values that are required for that MDX query, but this may result in granular SQL queries.
That is, when an MDX query is executed against the OLAP database, if data needed by the MDX query is not in the OLAP database, then one or more Structured Query Language (SQL) queries are generated. SQL may be described as a query language for inserting, updating, and retrieving data from a relational database. The one or more SQL queries are executed against the relational database to retrieve data from the relational database that is inserted into the OLAP database.
Thus, MDX query execution may result in many SQL queries being generated for relational database access. Some OLAP databases cache data to prevent querying for the same data more than once between query executions or within the same query execution. That is, the OLAP database uses a local cache to minimize query cost against the relational database. That is, members and cell values within a OLAP database are cached.